﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using System.Data;
using System.IO;

namespace Rebides
{
    /// <summary>
    /// Descrição: Classe para afetuar as operações na BD SQLite3
    /// Autor: Luís Costa Nº 6032
    /// </summary>
    class DataBase
    {
        private string dataSource = "rebides.s3db";
        private SQLiteConnection connect;
        string strYear;

        /// <summary>
        /// Cria as tabelas na BD
        /// </summary>
        internal void create_tables()
        {
            try
            {
                //Establece uma ligação à BD
                //-----------------------------
                SQLiteConnection connect = new SQLiteConnection("Data Source=" + dataSource);
                connect.Open();

                SQLiteCommand sqlCMD = new SQLiteCommand(connect);

                //Cria a tabela de Tipos de Graus
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Tipos_Graus" +
                                        "(Cod_Tipo_Grau INTEGER PRIMARY KEY," +
                                            "Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Insere os dados na tabela
                sqlCMD.CommandText = "INSERT INTO Tipos_Graus VALUES (1, 'Licenciatura')";
                sqlCMD.ExecuteNonQuery();
                sqlCMD.CommandText = "INSERT INTO Tipos_Graus VALUES (2, 'Mestrado')";
                sqlCMD.ExecuteNonQuery();
                sqlCMD.CommandText = "INSERT INTO Tipos_Graus VALUES (3, 'Doutoramento')";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Graus
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Graus" +
                                        "(Cod_Grau INTEGER PRIMARY KEY AUTOINCREMENT," +
                                            "Cod_Tipo_Grau INTEGER," +
                                            "Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Cursos
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Cursos" +
                                        "(Cod_Curso INTEGER PRIMARY KEY AUTOINCREMENT," +
                                            "Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Categorias
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Categorias" +
                                        "(Cod_Categoria INTEGER PRIMARY KEY AUTOINCREMENT," +
                                            "Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Regimes
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Regimes" +
                                        "(Cod_Regime INTEGER PRIMARY KEY AUTOINCREMENT," +
                                            " Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Estabelecimentos
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Estabelecimentos" +
                                        "(Cod_Estabelecimento INTEGER PRIMARY KEY AUTOINCREMENT," +
                                            "Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Tipos de Estabelecimento
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Tipos_Estabelecimento" +
                                        "(Cod_Tipo_Estabelecimento INTEGER PRIMARY KEY," +
                                            "Designacao TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Docentes
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Docentes" +
                                        "(Cod_Docente INTEGER PRIMARY KEY," +
                                            "Nome_Completo TEXT)";
                sqlCMD.ExecuteNonQuery();

                //Cria a tabela de Fichas de Docência
                //-----------------------------
                sqlCMD.CommandText = "CREATE TABLE IF NOT EXISTS Fichas_Docencia" +
                                        "(Cod_Fichas_Docencia INTEGER PRIMARY KEY AUTOINCREMENT," +
                                            "Ano INTEGER," +
                                            "Cod_Docente INTEGER," +
                                            "Cod_Estabelecimento INTEGER," +
                                            "Cod_Categoria INTEGER," +
                                            "Cod_Regime INTEGER," +
                                            "Cod_Tipo_Estabelecimento INTEGER," +
                                            "Cod_Curso INTEGER," +
                                            "Cod_Grau INTEGER)";
                sqlCMD.ExecuteNonQuery();

                connect.Close();
            }

            catch (Exception e)
            {
                throw (e);
            }

        }

        /// <summary>
        /// Processa os dados recebidos e atualiza a BD
        /// </summary>
        /// <param name="lstData">Dados para inserir na BD</param>
        /// <param name="year">Ano que está a processar</param>
        internal void process_data(List<List <string>> lstData, int year)
        {
            //Guarda o ano que está a ser tratado e formata-o
            if (year < 10)
            {
                this.strYear = "0" + year.ToString();
            }
            else
            {
                this.strYear = year.ToString();
            }

            this.strYear = "20" + this.strYear.ToString();

            //Inicia o processamento dos dados para as tabelas auxiliares
            //---------------------------------------------------------
            Debug.WriteLine("A iniciar o processamento dos dados para as tabelas auxiliares");
            Debug.WriteLine("--------------------------");

            //Processa os dados para a tabela Docentes
            //-------------------
            Debug.WriteLine("A processar os docentes...");
            process_docentes(lstData[0], lstData[1]);
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela Categorias
            //-------------------
            Debug.WriteLine("A processar as categorias...");
            process_table(lstData[4], "Categorias");
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela de Cursos
            //-------------------
            Debug.WriteLine("A processar os cursos...");
            process_table(lstData[3], "Cursos");
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela Estabelecimentos
            //-------------------
            Debug.WriteLine("A processar os Estabelecimentos...");
            process_table(lstData[7], "Estabelecimentos");
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela Graus
            //-------------------
            Debug.WriteLine("A processar os graus...");
            process_graus(lstData[2], "Graus");
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela Regimes
            //-------------------
            Debug.WriteLine("A processar os regimes...");
            process_table(lstData[5], "Regimes");
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela Tipos_Estabelecimento
            //-------------------
            Debug.WriteLine("A processar os tipos de estabelecimento...");
            process_table(lstData[6], "Tipos_Estabelecimento");
            Debug.WriteLine("Processamento terminado com sucesso!");

            //Processa os dados para a tabela Fichas_Docencia
            //-------------------
            Debug.WriteLine("A processar as fichas de docencia...");
            process_fichas_docencia(lstData);
            Debug.WriteLine("Processamento terminado com sucesso!");
        }

        /// <summary>
        /// Processa os dados para a tabela de graus
        /// </summary>
        /// <param name="lstValues">Valores a inserir na BD</param>
        /// <param name="tableName">Nome da tabela a tratar</param>
        private void process_graus(List<string> lstValues, string tableName)
        {
            string strSQL;

            //Remove os duplicados
            //------------------------
            Debug.WriteLine("Total de registos iniciais: " + lstValues.Count.ToString());
            lstValues = lstValues.Distinct().ToList();
            Debug.WriteLine("Total de registos únicos: " + lstValues.Count.ToString());

            //Trata os apostrofos
            //lstValues = this.clear_apostrophe(lstValues);

            //Vai buscar os registos que já existem na BD
            //--------------------------
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            SQLiteCommand sqlCMD = new SQLiteCommand();

            strSQL = "SELECT     Designacao " +
                     "FROM " + tableName;

            try
            {
                DataSet dsValues = loadData(strSQL, tableName);
                Debug.WriteLine("Total de registos na BD: " + dsValues.Tables[0].Rows.Count);

                //Coloca os dados da BD numa lista
                //---------------
                List<string> lstOldValues = new List<string>();

                foreach (DataRow row in dsValues.Tables[0].Rows)
                {
                    lstOldValues.Add(row[0].ToString());
                }

                //Devolve uma lista com os registos que não estão na BD
                IEnumerable<string> lstNewValues = lstValues.Except(lstOldValues, StringComparer.OrdinalIgnoreCase);

                Debug.WriteLine("Total de registos a inserir: " + lstNewValues.Count());

                if (lstNewValues.Count() > 0)
                {               
                    this.createConn();
                    this.connect.Open();
                    int codTipoGrau;

                    //Cria uma transação
                    SQLiteTransaction transact = this.connect.BeginTransaction();

                    //Cria a string de SQL para o insert
                    strSQL = "INSERT INTO " + tableName + " (Cod_Tipo_Grau, Designacao) VALUES (@Cod_Tipo_Grau, @Designacao)";

                    sqlCMD = this.createCMDInsert(strSQL);
                    da.InsertCommand = sqlCMD;

                    //Insere os novos registos na BD
                    foreach (string value in lstNewValues)
                    {
                        //Verifica a que tipo de grau o grau atual pertence
                        codTipoGrau = get_tipo_grau(value);
                        
                        //Define os parametros do comando de SQL
                        sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Tipo_Grau", codTipoGrau));
                        sqlCMD.Parameters.Add(new SQLiteParameter("@Designacao", value));

                        //Executa o comando
                        sqlCMD.ExecuteNonQuery();

                        //Limpa os parametros
                        sqlCMD.Parameters.Clear();
                    }
                    transact.Commit();
                    this.connect.Close();
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        /// <summary>
        /// Verifica qual a que tipo de grau o grau atual pertence
        /// </summary>
        /// <param name="value">Grau a testar</param>
        /// <returns>O cód. do tipo de grau encontrado</returns>
        private int get_tipo_grau(string value)
        {
            int codTipoGrau = 4;
            string testValue = value.ToLower();

            //Teste de LICENCIATURA
            //-----------------------
            List <string> lstKeyWords = new List<string>{"licenciatura", "curso superior",
                                            "licenciado", "estudos superiores"};

            if (lstKeyWords.Contains(testValue))
            {
                codTipoGrau = 1;
            }
            
            //Teste de MESTRADO
            //----------------------
            if (codTipoGrau == 4)
            {
                List <string> lstKeyWords2 = new List<string>{"mestrado", "mestre", 
                                                "master", "mestra"};

                if (lstKeyWords2.Contains(testValue))
                {
                    codTipoGrau = 2;
                }
            }

            //Teste de DOUTORAMENTO
            //----------------------
            if (codTipoGrau == 4)
            {
                List <string> lstKeyWords3 = new List<string>{"doutoramento", "doctor",
                                                "docteur", "doctoris", "doktor",
                                                "doutorado", "doutorada"};

                if (lstKeyWords3.Contains(testValue))
                {
                    codTipoGrau = 3;
                }
            }
            return codTipoGrau;
        }


        /// <summary>
        /// Processa as tabelas auxiliares
        /// </summary>
        /// <param name="lstValues">Lista de dados a inserir na BD</param>
        /// <param name="tableName">Nome da tabela onde inserir onde dados</param>
        private void process_table(List<string> lstValues, string tableName)
        {
            string strSQL;

            //Remove os duplicados
            //------------------------
            Debug.WriteLine("Total de registos iniciais: " + lstValues.Count.ToString());
            lstValues = lstValues.Distinct().ToList();
            Debug.WriteLine("Total de registos únicos: " + lstValues.Count.ToString());

            //Trata os apostrofos
            //lstValues = this.clear_apostrophe(lstValues);

            //Vai buscar os registos que já existem na BD
            //--------------------------
            SQLiteDataAdapter da = new SQLiteDataAdapter();
            SQLiteCommand sqlCMD = new SQLiteCommand();

            strSQL = "SELECT     Designacao " +
                     "FROM " + tableName;

            try
            {
                DataSet dsValues = loadData(strSQL, tableName);
                Debug.WriteLine("Total de registos na BD: " + dsValues.Tables[0].Rows.Count);

                //Coloca os dados da BD numa lista
                //---------------
                List<string> lstOldValues = new List<string>();

                foreach (DataRow row in dsValues.Tables[0].Rows)
                {
                    lstOldValues.Add(row[0].ToString());
                }

                //Devolve uma lista com os registos que não estão na BD
                IEnumerable<string> lstNewValues = lstValues.Except(lstOldValues, StringComparer.OrdinalIgnoreCase);

                Debug.WriteLine("Total de registos a inserir: " + lstNewValues.Count());

                if (lstNewValues.Count() > 0)
                {
                    this.createConn();
                    this.connect.Open();

                    //Cria uma transação
                    SQLiteTransaction transact = this.connect.BeginTransaction();

                    //Cria a string de SQL para o insert
                    strSQL = "INSERT INTO " + tableName + " (Designacao) VALUES (@value)";

                    sqlCMD = this.createCMDInsert(strSQL);
                    da.InsertCommand = sqlCMD;

                    //Insere os novos registos na BD
                    foreach (string value in lstNewValues)
                    {
                        //Define os parametros do comando de SQL
                        sqlCMD.Parameters.Add(new SQLiteParameter("@value", value));

                        //Executa o comando
                        sqlCMD.ExecuteNonQuery();

                        //Limpa os parametros
                        sqlCMD.Parameters.Clear();
                    }
                    transact.Commit();
                    this.connect.Close();
                }
            }
            catch (Exception e)
            {
                throw (e);
            }

        }

        /// <summary>
        /// Processa os dados para a tabela de Fichas de Docência.
        /// Esta é a tabela principal que relaciona todas as outras.
        /// Cria vários dicionários de modo ir buscar as chaves de cada registos
        /// de modo a só inserir as chaves.
        /// </summary>
        /// <param name="lstData"> Dicionario com os dados do CSV</param>
        private void process_fichas_docencia(List<List <string>> lstData)
        {
            string strSQL;

            ///Cria uma lista para os dicionarios com os dados de cada tabela da BD
            ///-------------------------
            List <Dictionary<string, string>> dictBDDados = new List<Dictionary<string, string>>();

            List<string> tempTableNames = new List<string> 
                {"Graus", "Cursos", "Categorias", "Regimes", "Tipos_Estabelecimento",
                   "Estabelecimentos"};

            List<string> tempKeyNames = new List<string> 
                {"Cod_Grau", "Cod_Curso", "Cod_Categoria", "Cod_Regime", "Cod_Tipo_Estabelecimento",
                   "Cod_Estabelecimento"};

            ///Cria um dicionário para cada uma das tabelas auxiliares
            ///---------------------
            for (int n = 0; n <= 5; n++)
            {
                Debug.WriteLine("A criar o dicionário para a tabela de " + tempTableNames[n]);
                dictBDDados.Add(table_to_dict(tempTableNames[n], tempKeyNames[n], "Designacao"));
                Debug.WriteLine("Dicionário criado com sucesso!");
            }

            try
            {
                Debug.WriteLine("A limpar os dados do ano " + this.strYear);
                //Limpa os dados da tabela para este ano
                //-----------------
                strSQL = "DELETE FROM Fichas_Docencia WHERE Ano = " + this.strYear;

                this.createConn();
                this.connect.Open();

                this.execSQL(strSQL);

                //Insere na BD os dados.
                //Por cada registo que está na lstDados com os dados do CSV vai procurar no dicionario
                //correspondente o código gerado aquando da inserção do registo na BD.
                //Desta forma cria-se uma base de dados relacional sendo a tabela principal a tabela Fichas_Docencia
                //----------------------
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                SQLiteCommand sqlCMD = new SQLiteCommand();

                //Cria uma transação
                SQLiteTransaction transact = this.connect.BeginTransaction();

                //Cria a string de SQL para o insert
                strSQL = "INSERT INTO Fichas_Docencia " +
                            "(" +
                            "Ano, " +
                            "Cod_Docente, " +
                            "Cod_Estabelecimento, " +
                            "Cod_Categoria, " +
                            "Cod_Regime, " +
                            "Cod_Tipo_Estabelecimento, " +
                            "Cod_Grau, " +
                            "Cod_Curso) " +
                            "VALUES" +
                            " (" +
                            "@Ano, " +
                            "@Cod_Docente, " +
                            "@Cod_Estabelecimento, " +
                            "@Cod_Categoria, " +
                            "@Cod_Regime, " +
                            "@Cod_Tipo_Estabelecimento, " +
                            "@Cod_Grau, " +
                            "@Cod_Curso)";

                sqlCMD = this.createCMDInsert(strSQL);
                da.InsertCommand = sqlCMD;
                int teste;
                Debug.WriteLine("A inserir os registos das Fichas de Docência...");
                for (int n = 0; n < lstData[0].Count(); n++)
                {

                    //Define os parametros do comando de SQL
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Ano", this.strYear));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Docente", lstData[0][n]));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Estabelecimento", dictBDDados[5][lstData[7][n]]));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Categoria", dictBDDados[2][lstData[4][n]]));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Regime", dictBDDados[3][lstData[5][n]]));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Tipo_Estabelecimento", dictBDDados[4][lstData[6][n]]));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Grau", dictBDDados[0][lstData[2][n]]));
                    sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Curso", dictBDDados[1][lstData[3][n]]));


                    //Executa o comando
                    sqlCMD.ExecuteNonQuery();

                    //Limpa os parametros
                    sqlCMD.Parameters.Clear();

                    teste = n;
                }
                transact.Commit();
                this.connect.Close();

                Debug.WriteLine("Registos inseridos com sucesso!");
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        /// <summary>
        /// Cria um dicionário a partir dos dados de uma tabela da BD.
        /// A key do dicionario será o campo de texto
        /// </summary>
        /// <param name="tableName">Nome da tabela</param>
        /// <param name="fieldName1">Campo 1</param>
        /// <param name="fieldName2">Campo 2</param>
        /// <returns></returns>
        private Dictionary<string, string> table_to_dict(string tableName, string fieldName1, string fieldName2)
        {
            string strSQL;
            SQLiteCommand sqlCMD = new SQLiteCommand();

            //Carrega os dados da tabela
            //---------------
            strSQL = "SELECT " + fieldName1 + ", " + fieldName2 + 
                     " FROM " + tableName;

            DataSet dsValues = loadData(strSQL, tableName);

            //Coloca os dados num dicionario
            //-----------------
            Dictionary<string, string> dictData = new Dictionary<string, string>();
            
            foreach(DataRow row in dsValues.Tables[0].Rows)
            {
                dictData.Add(row[1].ToString(), row[0].ToString());
            }

            return dictData;
        }

        /// <summary>
        /// Processa os dados para a tabela de Docentes
        /// </summary>
        /// <param name="lstCod">Lista com os códigos do docentes</param>
        /// <param name="lstNome">Lista com os nomes dos docentes</param>
        private void process_docentes(List<string> lstCod, List<string> lstNome)
        {

            string strSQL;

            SQLiteDataAdapter da = new SQLiteDataAdapter();
            SQLiteCommand sqlCMD = new SQLiteCommand();

            //Remove os duplicados
            //---------------------
            List<List<string>> newDocentes = remove_duplicate_docentes(lstCod, lstNome);

            //Trata os nomes dos docentes para retirar os apostrofos
            //------------------------
            newDocentes[1] = clear_apostrophe(newDocentes[1]);

            //Verifica se a BD tem registos

            strSQL = "SELECT     * " +
                     "FROM       Docentes " +
                     "ORDER BY   Cod_Docente ";
            try
            {
                DataSet dsDocentes = loadData(strSQL, "Docentes");
                Debug.WriteLine("Total de docentes na BD: " + dsDocentes.Tables[0].Rows.Count);

                //Verifica se a tabela está vazia. Se estiver insere todos os campos sem
                //efetuar o teste para ver se já existem na BD
                //--------------------------

                //Abre a ligação à BD
                //---------------
                createConn();
                this.connect.Open();
           
                if (dsDocentes.Tables[0].Rows.Count > 0)
                {
                    //Coloca os dados da BD numa lista
                    //---------------
                    List<List<string>> oldDocentes = new List<List<string>>();
                    oldDocentes.Add(new List<string>());
                    oldDocentes.Add(new List<string>());

                    foreach (DataRow row in dsDocentes.Tables[0].Rows)
                    {
                        oldDocentes[0].Add(row["Cod_Docente"].ToString());
                        oldDocentes[1].Add(row["Nome_Completo"].ToString());
                    }
                    
                    //Remove os docentes duplicados. Deste modo os docentes que já estão
                    //na BD não serão inseridos.

                    newDocentes = this.remove_duplicate_docentes_BD(newDocentes, oldDocentes);

                    Debug.WriteLine("A inserir os docentes na BD...");

                    //Cria um SQL command para inserir os dados e adicionao a um adapter
                    //---------------------------

                    //Cria uma transação
                    SQLiteTransaction transact = this.connect.BeginTransaction();

                    sqlCMD = this.createCMDInsertDocentes();
                    da.InsertCommand = sqlCMD;

                    //Insere os registos em falta na BD
                    //-----------------------
                    for (int n = 0; n < newDocentes[0].Count(); n++)
                    {
                        //Define os parametros do comando de SQL
                        sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Docente", newDocentes[0][n]));
                        sqlCMD.Parameters.Add(new SQLiteParameter("@Nome_Completo", newDocentes[1][n]));

                        sqlCMD.ExecuteNonQuery();

                        sqlCMD.Parameters.Clear();
                        ////Atualiza a BD
                        ////-------------------
                        //da.Update(dsDocentes, "Docentes");
                    }
                    transact.Commit();
                }
                else
                {
                    Debug.WriteLine("A inserir os docentes na BD...");

                    //Cria um SQL command para inserir os dados e adicionao a um adapter
                    //---------------------------

                    //Cria uma transação
                    SQLiteTransaction transact = this.connect.BeginTransaction();

                    sqlCMD = this.createCMDInsertDocentes();
                    da.InsertCommand = sqlCMD; 

                    //Insere os registos em falta na BD
                    //-----------------------
                    for (int n = 0; n < newDocentes[0].Count(); n++)
                    {
                        //Cria um novo row para o dataset
                        //--------------
                        //Define os parametros do comando de SQL
                        sqlCMD.Parameters.Add(new SQLiteParameter("@Cod_Docente", newDocentes[0][n]));
                        sqlCMD.Parameters.Add(new SQLiteParameter("@Nome_Completo", newDocentes[1][n]));

                        sqlCMD.ExecuteNonQuery();

                        sqlCMD.Parameters.Clear();
                        ////Atualiza a BD
                        ////-------------------
                        //da.Update(dsDocentes, "Docentes");
                    }
                    transact.Commit();
                }
                
                this.closeConn();
            }
            catch (Exception e)
            {
                this.closeConn();

                throw (e);
            }
        }

        /// <summary>
        /// Remove os docentes duplicados
        /// </summary>
        /// <param name="lstCod">Cód. dos docentes</param>
        /// <param name="lstNome">Nome dos docentes</param>
        /// <returns>Uma lista com o Cód. e Nome dos docentes</returns>
        private List<List<string>> remove_duplicate_docentes(List<string> lstCod, List<string> lstNome)
        {
            Debug.WriteLine("A remover os docentes duplicados do CSV...");

            List<List<string>> docentes = new List<List<string>>();
            docentes.Add(new List<string>());
            docentes.Add(new List<string>());

            for (int n = 0; n < lstCod.Count; n++)
            {
                if (docentes[0].Contains(lstCod[n]) == false)
                {
                    //Adiciona os docentes à lista a devolver
                    docentes[0].Add(lstCod[n]);
                    docentes[1].Add(lstNome[n]);
                }
            }

            Debug.WriteLine("Total de docentes iniciais: " + lstCod.Count());
            Debug.WriteLine("Total de docentes atuais: " + docentes[0].Count());

            return docentes;
        }

        /// <summary>
        /// 'Remove os docentes que já existem na BD
        /// </summary>
        /// <param name="newDocentes">Lista de docentes do CSV</param>
        /// <param name="dsDocentes">Lista de docentes na BD</param>
        /// <returns>Lista de novos docentes</returns>
        private List<List<string>> remove_duplicate_docentes_BD(List<List<string>> newDocentes, List<List<string>> oldDocentes)
        {
            Debug.WriteLine("A procurar pelos docentes que não estão na BD...");

            List<List<string>> docentes = new List<List<string>>();
            docentes.Add(new List<string>());
            docentes.Add(new List<string>());

            for (int n = 0; n < newDocentes[0].Count; n++)
            {
                if (oldDocentes[0].Contains(newDocentes[0][n]) == false)
                {
                    //Adiciona os docentes à lista a devolver
                    docentes[0].Add(newDocentes[0][n]);
                    docentes[1].Add(newDocentes[1][n]);
                }
            }

            Debug.WriteLine("Total de docentes iniciais: " + oldDocentes[0].Count());
            Debug.WriteLine("Total de docentes atuais: " + docentes[0].Count());

            return docentes;
        }

        /// <summary>
        /// Trata as apostrofes de uma lista de strings.
        /// Acrescente mais duas apostrofes de modo a não perturbar o script de SQL.
        /// </summary>
        /// <param name="lstStr">Lista de strings a tratar</param>
        /// <returns>Uma lista de strings</returns>
        private List<string> clear_apostrophe(List<string> lstStr)
        {
            List<string> newData = new List<string>();

            foreach (string str in lstStr)
            {
                newData.Add(str.Replace("'", "''"));
            }
            return newData;
        }

        /// <summary>
        /// Cria uma ligação à BD
        /// </summary>
        private void createConn()
        {
            this.connect = new SQLiteConnection("Data Source=" + dataSource);
        }

        /// <summary>
        /// Fecha a ligação à BD
        /// </summary>
        private void closeConn()
        {
            this.connect.Close();
        }

        /// <summary>
        /// Le os dados da BD e carrega um DataSet
        /// </summary>
        /// <param name="strSQL">String de SQL</param>
        public DataSet loadData(string strSQL, string strTabel)
        {
            try
            {
                //Abre a ligação à BD
                createConn();
                this.connect.Open();

                //Cria um DataAdapter para correr a string de SQL
                SQLiteDataAdapter da = new SQLiteDataAdapter(strSQL, connect);

                //Coloca os dados num DataSet
                DataSet dsTemp = new DataSet();
                da.Fill(dsTemp, strTabel);

                closeConn();

                return dsTemp;
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        /// <summary>
        /// Executa um comando de SQL
        /// </summary>
        /// <param name="strSQL">Comando de SQL a executar</param>
        private void execSQL(string strSQL)
        {
            //Cria um comando para enviar o SQL
            SQLiteCommand sqlCMD = new SQLiteCommand();
            sqlCMD = this.connect.CreateCommand();
            sqlCMD.CommandText = strSQL;

            //Executa o query
            sqlCMD.ExecuteNonQuery();
        }

        /// <summary>
        /// Cria um comando de SQL para inserir os dados dos docentes
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        private SQLiteCommand createCMDInsertDocentes()
        {
            string strSQL;

            //Cria um comando para enviar o SQL
            SQLiteCommand sqlCMD = new SQLiteCommand();
            sqlCMD.Connection = this.connect;

            strSQL = "INSERT INTO Docentes VALUES (@Cod_Docente, @Nome_Completo)";
            sqlCMD.CommandText = strSQL;

            return sqlCMD;
        }

        /// <summary>
        /// Cria uma instrução de INSERT
        /// </summary>
        /// <param name="strSQL">Instrução de INSERT</param>
        /// <returns>Comando para a instrução de INSERT</returns>
        private SQLiteCommand createCMDInsert(string strSQL)
        {
            //Cria um comando para enviar o SQL
            SQLiteCommand sqlCMD = new SQLiteCommand();
            sqlCMD.Connection = this.connect;

            sqlCMD.CommandText = strSQL;

            return sqlCMD; 
        }
    }
}
